/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package functions;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Catalog;
import model.Item;
import model.Database;
import model.Distributor;
import model.Sklad;
import model.Sklad_drugs;

/**
 *
 * @author JArik
 */
public class Items {
   private String query;
    private boolean groupByName,
                    groupByDate,
                    groupByDist;
    private String items, sklad, distributors;
    public Items()
    {
        query = " SD.currentAmount>0 ";
        groupByName = false;
        groupByDate = false;
        groupByDist =false;
        distributors = "distributors";
        sklad = "skladDetailed";
        items = "drugs";
    }
        public void searchByName(String name)
    {
        query += "AND SD.name LIKE '%"+name+"%' ";
    }
    public void searchByDist(String dist)
    {
        query += "AND DI.name LIKE '%"+dist+"%' ";
    }
    public void searchByDates(java.util.Date date1, java.util.Date date2)
    {
        String date = (date1.getYear()+1900) + "-" + (date1.getMonth()+1) + "-"
                      + (date1.getDate());
        String sdate = (date2.getYear()+1900) + "-" + (date2.getMonth()+1) + "-"
                      + (date2.getDate());
        query += " AND date BETWEEN '" + date + "' AND '" + sdate +"' ";
    }
    public void searchByStrictType(String type)
    {
        query += "AND SD.type = '"+type +"' ";
    }
    public void searchByTypeNot(String type)
    {
       query += "AND SD.type != '"+type +"' ";
    }
    public void searchBySoftType(String type)
    {
        query += "AND SD.type = '"+type +"' OR SD.type ='' ";
    }
    public void searchInSklad(int id) {
        query +=" AND skl="+id + " ";
    }
    public void groupByName()
    {
        groupByName=true;
    }

    public ArrayList search() throws SQLException
    {
        if(groupByName)
        {
            query +=" GROUP BY SD.name ";
        }
        else
        {
            query +=" GROUP BY SD.rec_id ";
        }
        Database db = new Database();
        ResultSet set = db.getDBInfo(
                "SD.id, SD.distrId, SD.name, SD.date, SD.descr, " +
                "SD.sell, SD.sellMedium, SD.sellHigh, SD.currency, SUM(SD.currentAmount) AS amount, " +
                "DI.name AS distName, SD.buy, SD.rec_id, SD.type, SD.tax, SD.skl AS skladId, SD.skladName ",
"(SELECT SD.*, D.type, D.name "+
"FROM "+sklad+" SD LEFT JOIN "+items+" D ON SD.id = D.id) SD " +
"LEFT JOIN "+distributors+" DI ON DI.id = SD.distrId ",
                query);
        ArrayList<Item> list = new ArrayList<Item>();
        while ( set.next())
        {
            Item item = new Item();
            item.setInDate(set.getDate("date"));
            item.setId(set.getInt("id"));
            item.setName(set.getString("name"));
            item.setRecId(set.getInt("rec_id"));
            item.setDescr(set.getString("descr"));
            if(set.getInt("distrId") != 0 )
              item.setDistributor(new Distributor(set.getInt("distrId"), set.getString("distName")));
            item.setSklad(new Sklad(set.getInt("skladId"), set.getString("skladName")));
            item.setSellPrices(set.getDouble("sell"),set.getDouble("sellMedium"),set.getDouble("sellHigh"));
            item.setCurrency(set.getString("currency"));
            item.setAmount(set.getDouble("amount"));
            item.setBuyPrice(set.getDouble("buy"));
            item.setType(set.getString("type"));
            item.setTax(set.getDouble("tax"));
            
            list.add(item);
        }
        db.closeStatement();
        db = null;
        return list;
    }
    public static ArrayList<Item> getItemByName(String name) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("*", "drugs","name LIKE '%"+name+"%' and type='muud'  ORDER BY name ");
        ArrayList<Item> list = new ArrayList<Item>();
        while ( set.next())
        {
            Item item = new Item();
            item.setName(set.getString("name"));
            item.setId(set.getInt("id"));
            list.add(item);

        }
        db.closeStatement();
        db = null;
        return list;
    }

    public static int addToCatalog(Item item) throws SQLException
    {
        String what ="", values ="";
        what = "name";
        if( item.getName() != null && !item.getName().isEmpty())
          values = "'"+item.getName()+"'";
        else throw new SQLException("Введите наименование предмета");
        
        return Catalog.addItem(what, values);
    }
    public static int  addToSklad(Item item, double currentAmount,String currency, int skladId, double tax) throws SQLException
    {
        String values="", what="";
        if(item.getId() !=0)
        {
          what +="id";
          values += ""+item.getId()+"";
        } else throw new SQLException("Введите название лекарства");
        
        if(tax != 0)
        {
          what +=",tax";
          values += ","+tax+"";
        } else throw new SQLException("Укажите налог.");

        if(currency != null && !currency.isEmpty())
        {
          what +=",currency";
          values += ",'"+currency+"'";
        } else throw new SQLException("Укажите валюту.");

        if(item.getBuyPrice() != -1)
        {
            what +=",buy";
            values += ","+item.getBuyPrice()+"";
        }
        if(item.getSellPrices() != null)
        {
            what +=", sell, sellMedium, sellHigh";
            values +=","+item.getSellPrices()[0] +","+item.getSellPrices()[1]+","+item.getSellPrices()[2];
        }/*
        if ( currentAmount != -1)
        {
            what +=",currentAmount";
            values +=","+currentAmount +"";
        }*/
         if(currentAmount > 0)
        {
            what +=",currentAmount";
            values +=","+currentAmount +"";
        } else throw new SQLException("Неправельное кол-во товара");
        if(item.getDescr() != null)
        {
            what +=",descr";
            values +=",'"+item.getDescr() +"'";
        }
        if(item.getInDate() != null)
        {
            what +=",date";
            values +=",'"+item.getInSQLDate() +"'";
        } else
            throw new SQLException("Укажите дату ввоза товара.");
        if(item.getDist() != null)
        {
            what +=",distrId";
            values +=","+item.getDist().getId() +"";
        }
        if(skladId != -1)
        {
            what +=",skl ";
            values +=","+skladId +"";
        }
        return Sklad_drugs.add(what,values);
    }
     public static int dischargeItem(int visId, int skladId, int dogId, int docId,
                                     double sellPrice, Date disDate, String descr,
                                     double payed, double amount) throws SQLException
    {
        String values="", what="";
        
        if(visId > 0)
        {
          what +="visitId";
          values += ""+visId+"";
        } else throw new SQLException("Неправельный номер визита: " + visId);

        if(sellPrice >= 0)
        {
            what +=",sellPrice";
            values += ","+sellPrice+"";
        }  else throw new SQLException("Неуказана цена продажи: " + sellPrice);
        if(dogId != 0)
        {
            what +=",dogId";
            values += ","+dogId+"";
        } else throw new SQLException("Неуказано животное");
        if(skladId != 0)
        {
            what +=",drugId";
            values += ","+skladId+"";
        } else throw new SQLException("Товара нет на складе");
        if(docId != 0)
        {
            what +=",exec";
            values += ","+docId+"";
        } else throw new SQLException("Неуказан врач");
        if(disDate != null)
        {
            disDate = new Date(disDate.getTime());
            what +=",date";
            values += ",'"+disDate+"'";
        } else throw new SQLException("Неправельная дата выдачи: " + disDate);
        if(descr != null)
        {
            what +=",descr";
            values +=",'"+descr +"'";
        }
        if( payed >= 0)
        {
            what +=",payed";
            values +=","+payed+" ";
        } else throw new SQLException("Неправельная цена за товар: " + payed);
        if( amount > 0)
        {
            what +=",amount";
            values +=","+amount+" ";
        } else throw new SQLException("Неправельное количество товара: " + amount);

        
        
        return Sklad_drugs.dischargeDrug(what, values, skladId, amount);
     }
     
     public static int updateDisItem(int visId, int skladId, int dogId, int docId,
                                     double sellPrice, Date disDate, String descr,
                                     double payed, double amount)
                                     throws SQLException
    {
        String values="", what="";
        
        if(visId > 0)
        {
          what +="visitId";
          values += ""+visId+"";
        } else throw new SQLException("Неправельный номер визита: " + visId);

        if(sellPrice >= 0)
        {
            what +=",sellPrice";
            values += ","+sellPrice+"";
        }  else throw new SQLException("Неуказана цена продажи: " + sellPrice);
        if(dogId != 0)
        {
            what +=",dogId";
            values += ","+dogId+"";
        } else throw new SQLException("Неуказано животное");
        if(skladId != 0)
        {
            what +=",drugId";
            values += ","+skladId+"";
        } else throw new SQLException("Товара нет на складе");
        if(docId != 0)
        {
            what +=",exec";
            values += ","+docId+"";
        } else throw new SQLException("Неуказан врач");
        if(disDate != null)
        {
            disDate = new Date(disDate.getTime());
            what +=",date";
            values += ",'"+disDate+"'";
        } else throw new SQLException("Неправельная дата выдачи: " + disDate);
        if(descr != null)
        {
            what +=",descr";
            values +=",'"+descr +"'";
        }
        if( payed >= 0)
        {
            what +=",payed";
            values +=","+payed+" ";
        } else throw new SQLException("Неправельная цена за товар: " + payed);
        if( amount > 0)
        {
            what +=",amount";
            values +=","+amount+" ";
        } else throw new SQLException("Неправельное количество товара: " + amount);

        
        return Sklad_drugs.dischargeDrug(what, values, skladId, amount);
     }

    public static int recordAsDischargedItem(int visId, int skladId, int dogId, int docId,
                                     double sellPrice, Date disDate, String descr,
                                     double payed, double amount) throws SQLException
    {
        String values="", what="";
        
        if(visId > 0)
        {
          what +="visitId";
          values += ""+visId+"";
        } else throw new SQLException("Неправельный номер визита: " + visId);

        if(sellPrice >= 0)
        {
            what +=",sellPrice";
            values += ","+sellPrice+"";
        }  else throw new SQLException("Неуказана цена продажи: " + sellPrice);
        if(dogId != 0)
        {
            what +=",dogId";
            values += ","+dogId+"";
        } else throw new SQLException("Неуказано животное");
        if(skladId != 0)
        {
            what +=",drugId";
            values += ","+skladId+"";
        } else throw new SQLException("Товара нет на складе");
        if(docId != 0)
        {
            what +=",exec";
            values += ","+docId+"";
        } else throw new SQLException("Неуказан врач");
        if(disDate != null)
        {
            disDate = new Date(disDate.getTime());
            what +=",date";
            values += ",'"+disDate+"'";
        } else throw new SQLException("Неправельная дата выдачи: " + disDate);
        if(descr != null)
        {
            what +=",descr";
            values +=",'"+descr +"'";
        }
        if( payed >= 0)
        {
            what +=",payed";
            values +=","+payed+" ";
        } else throw new SQLException("Неправельная цена за товар: " + payed);
        if( amount > 0)
        {
            what +=",amount";
            values +=","+amount+" ";
        } else throw new SQLException("Неправельное количество товара: " + amount);



        return Sklad_drugs.recordAsDischargedDrug(what, values, skladId, amount);
     }


    
}